package com.surelution.projects.crm
import org.codehaus.groovy.grails.commons.ConfigurationHolder
import groovy.time.TimeCategory

import org.apache.jasper.compiler.Node.ParamsAction;
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import java.text.NumberFormat
import java.text.SimpleDateFormat
import java.util.Date;
import java.util.Map;
import org.grails.plugins.excelimport.ExcelImportUtils;


class MarketController {

    static allowedMethods = [save: "POST", update: "POST", delete: "POST"]
	
	def pictureService
	static Map CONFIG_MARKET_MAP = [
		sheet:"market",
		startRow: 2,
		columnMap:  [
			'B':'marketName',
			'C':'address',
			'D':'manager',
			'E':'managerTel',
			'F':'gasPredictSale',
			'G':'fuelPredictSale',
			'H':'provideSite',
			'I':'carePoint',
			'J':'requirement',
			'K':'betweenDistance',
		]
	]
    def index = {
        redirect(action: "report")
    }
	
	def reset = {
		def marketInstance = new Market()
		marketInstance.latitude = params.lat
		marketInstance.longitude = params.lng
		marketInstance.name = params.name
		
		OwnStation station1 = User.findByUsername(springSecurityService.principal.username).station
		
		def station = springSecurityService.principal.username=='admin'?'':station1
		def result = Market.createCriteria().list([max:params.max, offset:params.offset]){
			if(station){
				eq("station",station)
			}
			eq("isLocked", false)
		}
		if (!marketInstance) {
			flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'market.label', default: 'market'), params.id])}"
			redirect(action: "list")
		}
		else {
			return [marketInstance: marketInstance,marketList:result]
		}
	}
	
	def resetPosition = {
		def marketInstance = Market.get(params.id)
		if (marketInstance) {
			if (params.version) {
				def version = params.version.toLong()
				if (marketInstance.version > version) {
					marketInstance.errors.rejectValue("version", "default.optimistic.locking.failure", [message(code: 'market.label', default: 'market')] as Object[], "Another user has updated this OwnStation while you were editing")
					render(view: "edit", model: [marketInstance: marketInstance])
					return
				}
			}
			marketInstance.properties = params
			if (!marketInstance.hasErrors() && marketInstance.save(flush: true)) {
				flash.message = "${message(code: 'default.updated.message', args: [message(code: 'market.label', default: 'market'), marketInstance.id])}"
				redirect(action: "show", id: marketInstance.id)
			}
			else {
				render(view: "edit", model: [marketInstance: marketInstance])
			}
		}
		else {
			flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'market.label', default: 'market'), params.id])}"
			redirect(action: "list")
		}
	}
	
	def marketSaleReport = {
		def month
		def startDay = new Date()
		def endDay = new Date()
		def areaIds = []
		def mapList 
		OwnStation station = User.findByUsername(springSecurityService.principal.username).station
		if (params.month){
			month = params.month
			endDay = new Date(month.year,month.month+1,0)
			startDay = new Date(month.year,month.month,1)
			if(params.stationArea){
				def stationArea = params.stationArea
				if(stationArea instanceof String) {
					areaIds.add(Long.parseLong(stationArea))
				} else {
					stationArea.each {id->
						areaIds.add(Long.parseLong(id))
					}
				}
				mapList = marketSale(startDay,endDay,areaIds)
			}
			else{
				mapList = marketStationSale(startDay,endDay,station)
			}
		}
		[month:month,result:mapList,stationArea:areaIds,startDay:startDay,endDay:endDay]
	}
	
	def marketSaleReportExport = {
		def month,startDay,endDay
		def areaIds = []
		def mapList
		OwnStation station = User.findByUsername(springSecurityService.principal.username).station
		if (params.month){
			month = params.month
			endDay = new Date(month.year,month.month+1,0)
			startDay = new Date(month.year,month.month,1)
			if(params.stationArea){
				def stationArea = params.stationArea
				if(stationArea instanceof String) {
					areaIds.add(Long.parseLong(stationArea))
				} else {
					stationArea.each {id->
						areaIds.add(Long.parseLong(id))
					}
				}
				mapList = marketSale(startDay,endDay,areaIds)
			}
			else{
				mapList = marketStationSale(startDay,endDay,station)
			}
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=marketSale.xls")
			List fields = ['area','stationName','marketName','market.discountNum','monthgas','monthfuel','monthAll','yeargas','yearfuel','yearAll','contractAmount','monthFinish','compare']
			Map labels = [ 'area':"片区",'stationName':"站名",'marketName':"客户名称",'market.discountNum':"优惠额度",'monthgas':"柴油",'monthfuel':"汽油",'monthAll':"月度合计",'yeargas':"柴油",'yearfuel':"汽油",'yearAll':"年度合计",'compare':"环比",'contractAmount':"协议销量",'monthFinish':"兑现率"]
			exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
		}
	}
	 
	def marketSale(Date startDay, Date endDay,areaIds){
		Date year = new Date(startDay.year,0,1)
		def month = startDay.month-year.month
		def result,mResult
		def sb = new StringBuilder("select mgs.market,mgs.saleNumber,mgs.dieselOilNumber ")
		sb.append '''from MarketGasSale mgs where mgs.saleGasTime <= :endDay and mgs.saleGasTime >= :startDay and mgs.market.station.stationArea.id in(:areaIds) '''
		result = MarketGasSale.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		if (!result){
			flash.message = "${message(code: 'default.nofound.message')}"
			redirect(action: "marketSaleReport")
		}
		def smb = new StringBuilder("select mgs.market,sum(mgs.saleNumber),sum(mgs.dieselOilNumber) ")
		smb.append '''from MarketGasSale mgs where mgs.saleGasTime <= :endDay and mgs.saleGasTime >= :startDay and mgs.market.marketType.id=2 and mgs.market.station.stationArea.id in(:areaIds) group by mgs.market'''
		mResult = MarketGasSale.executeQuery(smb.toString(),['startDay':year, 'endDay':endDay,'areaIds':areaIds])
		def mapGasList = new ArrayList<HashMap>()
		result.each{
			def map = new HashMap()
			map.put("marketName",it[0].name)
			map.put("market",it[0])
			map.put("stationName",it[0].station.shortName)
			map.put("area",it[0].station.stationArea.areaName)
			map.put("monthgas",parseNumber(it[1].toString(), 1))
			map.put("monthfuel",parseNumber(it[2].toString(), 1))
			map.put("monthAll",parseNumber((it[1]+it[2]).toString(), 1))
			if (!it[0].contractAmount){
				map.put("monthFinish", 0)
			}
			else map.put("monthFinish",parseNumber(((it[1]+it[2])*100/it[0].contractAmount).toString(), 1))
			map.put("contractAmount",it[0].contractAmount)
			mResult.each{m->
				if(it[0] == m[0]){
					map.put("yeargas",parseNumber(m[1].toString(), 1))
					map.put("yearfuel",parseNumber(m[2].toString(), 1))
					map.put("yearAll",parseNumber((m[1]+m[2]).toString(), 1))
					map.put("compare",parseNumber(((it[1]+it[2])/(m[1]+m[2])*month).toString(), 1))
					mapGasList.add(map)
				}
			}
		}
		println mapGasList
		return mapGasList
	}
	
	def marketStationSale(Date startDay, Date endDay,station){
		Date year = new Date(startDay.year,0,1)
		def month = startDay.month-year.month
		def result,mResult
		def sb = new StringBuilder("select mgs.market,mgs.saleNumber,mgs.dieselOilNumber ")
		sb.append '''from MarketGasSale mgs where mgs.saleGasTime <= :endDay and mgs.saleGasTime >= :startDay and mgs.market.station = :station '''
		result = MarketGasSale.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'station':station])
		if (!result){
			flash.message = "${message(code: 'default.nofound.message')}"
			redirect(action: "marketSaleReport")
		}
		def smb = new StringBuilder("select mgs.market,sum(mgs.saleNumber),sum(mgs.dieselOilNumber) ")
		smb.append '''from MarketGasSale mgs where mgs.saleGasTime <= :endDay and mgs.saleGasTime >= :startDay and mgs.market.marketType.id=2 and mgs.market.station = :station group by mgs.market'''
		mResult = MarketGasSale.executeQuery(smb.toString(),['startDay':year, 'endDay':endDay,'station':station])
		def mapGasList = new ArrayList<HashMap>()
		result.each{
			def map = new HashMap()
			map.put("marketName",it[0].name)
			map.put("market",it[0])
			map.put("stationName",it[0].station.shortName)
			map.put("area",it[0].station.stationArea.areaName)
			map.put("monthgas",parseNumber(it[1].toString(), 1))
			map.put("monthfuel",parseNumber(it[2].toString(), 1))
			map.put("monthAll",parseNumber((it[1]+it[2]).toString(), 1))
			if (!it[0].contractAmount){
				map.put("monthFinish", 0)
			}
			else map.put("monthFinish",parseNumber(((it[1]+it[2])*100/it[0].contractAmount).toString(), 1))
			map.put("contractAmount",it[0].contractAmount)
			mResult.each{m->
				if(it[0] == m[0]){
					map.put("yeargas",parseNumber(m[1].toString(), 1))
					map.put("yearfuel",parseNumber(m[2].toString(), 1))
					map.put("yearAll",parseNumber((m[1]+m[2]).toString(), 1))
					map.put("compare",parseNumber(((it[1]+it[2])/(m[1]+m[2])*month).toString(), 1))
					mapGasList.add(map)
				}
			}
		}
		println mapGasList
		return mapGasList
	}
	
	def getValue(Row row ,int i){
		return getExcelCellValue(row.getCell(i))
	}
	
	def ImportExcelService
	def mImport = {
		def file = params.marketFile
		def fileName = file.originalFilename
		Workbook workbook = WorkbookFactory.create(file.inputStream)
		Sheet sheet = workbook.getSheetAt(0)
		ImportExcel excel = ImportExcelService.storeFile(file.inputStream,fileName)
		excel.uploader = springSecurityService.principal.username
		excel.type = "客户普查表"
		excel.save()
		def mapGasList = new ArrayList<HashMap>()
		((sheet.firstRowNum + 2) .. sheet.lastRowNum).each {rowNum->
			def map = new HashMap()
			Row row = sheet.getRow(rowNum)
			Market marketInstance = new Market(name:getValue(row,3),address:getValue(row,6),manager:getValue(row,7),managerTel:getValue(row,8),
				provideSite:getValue(row,12),carePoint:getValue(row,13),requirement:getValue(row,14),betweenDistance:getValue(row,15))
			marketInstance.marketName = getValue(row,0)
			marketInstance.station = OwnStation.findByCode(getValue(row,2))
			marketInstance.area = OwnStation.findByCode(getValue(row,2)).stationArea
			marketInstance.marketType = MarketSpecies.findByType(getValue(row,4))
			marketInstance.type = MarketType.findByTitle(getValue(row,5))
			marketInstance.gasPredictSale = Float.parseFloat(getValue(row,9))
			marketInstance.fuelPredictSale = Float.parseFloat(getValue(row,10))
			marketInstance.discountNum = getValue(row,11)
			marketInstance.firstSaleTime = new Date()
			marketInstance.startTime = new Date()
			marketInstance.createTime = new Date()
			marketInstance.isLocked = false
			marketInstance.detailedInfoValiable = false
			if (marketInstance.marketType.id != 1){
				int lastCellNum = row.lastCellNum
				if(lastCellNum > 15 && !Market.findByName(getValue(row,3))) {
					marketInstance.marketCardNo = getValue(row,16)
					marketInstance.save()
					(16 .. lastCellNum).each {
						def memberCardInstance = new MemberCard(cardNo:getValue(row,it),holderName:getValue(row,7),market:marketInstance,accountAcceptBranch:OwnStation.findByCode(getValue(row,2)).name)
						memberCardInstance.connectTime = new Date()
						memberCardInstance.isMaster = (it==16)
						memberCardInstance.accountAcceptDate = new Date()
						memberCardInstance.save()
						println memberCardInstance
						}
					}
			}
			else {
				marketInstance.save()
				println marketInstance
			}
		}
		redirect(action: "list")
	}
	
	def marketImport = {
		
	}
	
	/**
	 * 站内考核表
	 */
	def stationSumReport = {
		def month,endDay
		def now = new Date()
		def endDate = new Date(now.year,now.month,0)
		def startDay = new Date(now.year,now.month-1,1)
		def areaIds = []
		def mapList
		def stationList
		if (params.month){
			month = params.month
			startDay = month
			use(TimeCategory) {
				endDay = startDay + 1.month
				endDate = startDay + 1.month - 1.day
			}
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			stationList = stationSum(startDay,endDay)
		}
		[month:month,result:mapList,stationArea:areaIds,startDay:startDay,endDay:endDate,stationList:stationList]
	}
	
	def stationSum(Date startDay,Date endDay){
		def stationList = OwnStation.list()
		stationList.each {station->
			def monthSales = MarketGasSale.createCriteria().get{
				projections {
					sum("saleNumber")
					sum("dieselOilNumber")
				}
				createAlias('market','m')
				ge("saleGasTime", startDay)
				lt("saleGasTime", endDay)
				eq("sumType", MarketGasSale.SUM_TYPE_MONTH)
				eq("m.station", station)
			}
			def newMarketCount = Market.createCriteria().get{
				projections {
					count('id')
				}
				createAlias('marketType', 'mt')
				eq("station", station)
				eq("mt.id", 2l)
				ge("startTime", startDay)
				lt("startTime", endDay)
			}
			def newMonthSales = MarketGasSale.createCriteria().get{
				projections {
					sum("saleNumber")
					sum("dieselOilNumber")
				}
				createAlias('market','m')
				ge("saleGasTime", startDay)
				lt("saleGasTime", endDay)
				eq("sumType", MarketGasSale.SUM_TYPE_MONTH)
				eq("m.station", station)
				ge("m.startTime", startDay)
				lt("m.startTime", endDay)
			}
			//总客户数，包括持卡、潜在
			def marketCount = Market.createCriteria().get{
				projections {
					count("id")
				}
				eq("station", station)
			}
			
			//持卡客户数
			def cardHolderMarketCount = Market.createCriteria().get{
				projections {
					count("id")
				}
				createAlias('marketType', 'mt')
				eq("station", station)
				eq("mt.id", 2l)
			}
			
			//现金客户数
			def cashMarketCount = Market.createCriteria().get {
				projections {
					count("id")
				}
				createAlias('marketType', 'mt')
				eq("station", station)
				eq("mt.id", 3l)
			}
			
			//上月现金客户转持卡数量
			def cashToCardMarketCount = Market.createCriteria().get {
				projections {
					count("id")
				}
				eq("marketType.id", 2l)
				eq("preMarketType.id", 3l)
				eq("station", station)
				ge("startTime", startDay)
			}
			
			station.metaClass.getGasSale = {
				(monthSales&&monthSales[0])?monthSales[0]:0
			}
			station.metaClass.getDieselSale = {
				(monthSales&&monthSales[1])?monthSales[1]:0
			}
			station.metaClass.getMarketCount = {
				(marketCount)?marketCount:0
			}
			station.metaClass.getNewMarketCount = {
				(newMarketCount)?newMarketCount:0
			}
			station.metaClass.getNewGasSale = {
				(newMonthSales && newMonthSales[0])?newMonthSales[0]:0
			}
			station.metaClass.getNewDieselSale = {
				(newMonthSales && newMonthSales[1])?newMonthSales[1]:0
			}
			station.metaClass.getCardHolderMarketCount = {
				cardHolderMarketCount?cardHolderMarketCount:0
			}
			station.metaClass.getCashToCardRatio = {
				def total = cashToCardMarketCount + cashMarketCount
				(total==0)?1:(cashToCardMarketCount / total)
			}
			station.metaClass.getNewMarketTotalCount = {
				marketCount-cardHolderMarketCount
			}
			station.metaClass.getCashMarketCount = {
				cashMarketCount
			}
			station.metaClass.getNewToCardRatio = {
				(marketCount!=0)?(cardHolderMarketCount/marketCount*100):0
			}
			station.metaClass.getNewCountAll = {
				marketCount-cardHolderMarketCount
			}
		}
		return stationList
	}
		
	def stationSumReportExport = {
		def month,startDay,endDay
		def areaIds = []
		def mapList
		def stationList
		if (params.month&&params.stationArea){
			month = params.month
			startDay = month
			use(TimeCategory) {
				endDay = startDay + 1.month
			}
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			stationList = stationSum(startDay,endDay)
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=stationSum.xls")
			List fields = ['stationArea.areaName','shortName','gasSale','dieselSale','newGasSale','newDieselSale','cardHolderMarketCount','newMarketCount','newMarketTotalCount','newToCardRatio','cashMarketCount','cashToCardRatio']
			Map labels = [ 'stationArea.areaName':"片区",'shortName':"站名",'gasSale':"固定客户销量（汽油）",'dieselSale':"固定客户销量（柴油）",'newGasSale':"新增客户销量（汽油）",'newDieselSale':"新增客户销量（柴油）",'cardHolderMarketCount':"固定客户总数",'newMarketCount':"新增客户数",'newMarketTotalCount':"潜在客户总数",'newToCardRatio':"潜在客户转化率(%)",'cashMarketCount':"现金客户总数",'cashToCardRatio':"现金客户转换率(%)"]
			exportService.export("excel", response.outputStream, OwnStation.list(), fields, labels,[:],[:])
		}
	}
	
	/**
	 * 各站月度新增卡数量查询
	 */
	def cardReport = {
		def result
		def startDay,endDay
		def areaIds = []
		if (params.startDay&& params.endDay){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = cardSumReport(startDay,endDay,areaIds)
		}
		[result:result,startDay:startDay,endDay:endDay,stationArea:areaIds]
	}
	def cardReportExport = {
		def result
		def startDay,endDay
		def areaIds = []
		if (params.startDay&& params.endDay){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = cardSumReport(startDay,endDay,areaIds)
			
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=cardSum.xls")
			def mapList = new ArrayList<HashMap>()
			result.each {
				def map = new HashMap()
				def i = 2
				map.put("area", it[0].stationArea.areaName)
				map.put('stationName', it[1].name)
				map.put('cardSum', it[2])
				mapList.add(map)
			}
			List fields = ['area','stationName','cardSum']
			Map labels = [ 'area':"片区",'stationName':"站名",'cardSum':"新增卡数量"]
			exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
		}
		[result:result,startDay:startDay,endDay:endDay,stationArea:areaIds]
	}
	
	/**
	 * 分析各站新增卡数量
	 * @param startDay
	 * @param endDay
	 * @param areaIds
	 * @return
	 */
	def cardSumReport(Date startDay, Date endDay,areaIds){
		def result 
		def sb = new StringBuilder("select mc.market.station,mc.market")
		sb.append '''
				, count(mc.cardNo)
				from MemberCard mc where mc.connectTime <= :endDay and mc.connectTime >= :startDay and mc.market.station.stationArea.id in(:areaIds) group by mc.market.station
				order by count(mc.cardNo) DESC'''
		result = MemberCard.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		return result
	}
	
	def cardSaleReport = {
		def result
		def startDay,endDay
		def areaIds = []
		if (params.startDay&& params.endDay){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = cardSaleSumReport(startDay,endDay,areaIds)
		}
		[result:result,startDay:startDay,endDay:endDay,stationArea:areaIds]
	}
	def cardSaleReportExport = {
		def result
		def startDay,endDay
		def areaIds = []
		if (params.startDay&& params.endDay){
			startDay = params.startDay
			endDay = params.endDay
			def stationArea = params.stationArea
			if(stationArea instanceof String) {
				areaIds.add(Long.parseLong(stationArea))
			} else {
				stationArea.each {id->
					areaIds.add(Long.parseLong(id))
				}
			}
			result = cardSaleReport(startDay,endDay,areaIds)
			
			response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
			response.setHeader("Content-disposition", "attachment; filename=cardSum.xls")
			def mapList = new ArrayList<HashMap>()
			result.each {
				def map = new HashMap()
				def i = 2
				map.put("area", it[0][0].stationArea.areaName)
				map.put('stationName', it[0][0].name)
				map.put('cardSum', it[0][1])
				map.put('cardSumSale', it[0][2])
				mapList.add(map)
			}
			List fields = ['area','stationName','cardSum','cardSumSale']
			Map labels = [ 'area':"片区",'stationName':"站名",'cardSum':"新增卡数量",'cardSumSale':'新增卡销量']
			exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
		}
		[result:result,startDay:startDay,endDay:endDay,stationArea:areaIds]
	}
	
	/**
	 * 分析各站新增卡带来的总数量
	 * @param startDay
	 * @param endDay
	 * @param areaIds
	 * @return
	 */
	def cardSaleSumReport(Date startDay, Date endDay,areaIds){
		def result
		def sb = new StringBuilder("select mc.market.station,mc.market,count(mc.cardNo)")
		sb.append ''' from MemberCard mc where mc.connectTime <= :endDay and mc.connectTime >= :startDay and mc.market.station.stationArea.id in(:areaIds) 
				group by mc.market.station '''
		result = MemberCard.executeQuery(sb.toString(),['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		def mapList = []
		result.each{
			def station = it[0]
			def scb = new StringBuilder("select cc.member.market.station,cc.member")
			scb.append ''',sum(cc.amount)
				from CardConsume cc where cc.member.connectTime <= :endDay and cc.member.connectTime >= :startDay and cc.member.market.station = :station
					group by cc.member.market.station '''
			def cResult = CardConsume.executeQuery(scb.toString(),['startDay':startDay, 'endDay':endDay,'station':station])
			mapList.add(cResult)
		}
		mapList.each{
			result.each{r->
				if(r[0]==it[0][0])
				it[0][1] = r[2]
			}
		}
		println mapList
		return mapList
	}
	
	def parseNumber(String s,int i){
		NumberFormat nf = NumberFormat.getInstance();
		float d = Float.valueOf(s);
		nf.setMinimumFractionDigits(i);
		nf.setMaximumFractionDigits(i);
		def number = nf.parse(nf.format(d)).floatValue()
		return number
	}
	
	/**
	 * 购油量分析
	 */
    def report = {
		def result,endDay
		def startDay = new Date()
		use(TimeCategory) {
			endDay = startDay + 1.month
		}
		def areaIds = []
		OwnStation station = User.findByUsername(springSecurityService.principal.username).station
		Double saleNumberA,saleNumberB
		if (params.startDay&& params.endDay){
			startDay = params.startDay
			endDay = params.endDay
			try{
				saleNumberA = Double.parseDouble((params.saleNumberA).toString())
				saleNumberB = Double.parseDouble((params.saleNumberB).toString())
			}catch(e){}
			if(params.stationArea){
				def stationArea = params.stationArea
				if(stationArea instanceof String) {
					areaIds.add(Long.parseLong(stationArea))
				} else {
					stationArea.each {id->
						areaIds.add(Long.parseLong(id))
					}
				}
				result = marketReport(startDay,endDay,saleNumberA,saleNumberB,areaIds)
			}
			else{
				result = marketStationReport(startDay,endDay,saleNumberA,saleNumberB,station)
			}
		}
		[startDay:startDay,endDay:endDay,result:result,stationArea:areaIds,saleNumberA:saleNumberA,saleNumberB:saleNumberB]
    }

	def exportMarket ={
		def result
		def startDay,endDay
		def areaIds = []
		OwnStation station = User.findByUsername(springSecurityService.principal.username).station
		Double saleNumberA,saleNumberB
		if (params.startDay&& params.endDay){
			startDay = params.startDay
			endDay = params.endDay
			try{
				saleNumberA = Double.parseDouble((params.saleNumberA).toString())
				saleNumberB = Double.parseDouble((params.saleNumberB).toString())
			}catch(e){}
			if(params.stationArea){
				def stationArea = params.stationArea
				if(stationArea instanceof String) {
					areaIds.add(Long.parseLong(stationArea))
				} else {
					stationArea.each {id->
						areaIds.add(Long.parseLong(id))
					}
				}
				result = marketReport(startDay,endDay,saleNumberA,saleNumberB,areaIds)
			}
			else{
				result = marketStationReport(startDay,endDay,saleNumberA,saleNumberB,station)
			}
		}
		response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
		response.setHeader("Content-disposition", "attachment; filename=marketReport.xls")
		
		def mapList = new ArrayList<HashMap>()
		result.each {
			def map = new HashMap()
			map.put("area", it[0].area.areaName)
			map.put("name", it[0].name)
			map.put("marketCardNo", it[0].marketCardNo)
			map.put("address", it[0].address)
			map.put("manager", it[0].manager)
			map.put("managerTel", it[0].managerTel)
			map.put("annualSaleNumber", it[1])
			map.put("startDay", it[0].startTime.format("yyyy-MM-dd"))
			map.put("firstSaleTime", it[0].firstSaleTime.format("yyyy-MM-dd"))
			mapList.add(map)
		}
		List fields = ['area','name','marketCardNo','address','manager','managerTel','firstSaleTime','startDay','annualSaleNumber']
		Map labels = ['area':"片区",'name':"客户名称",'marketCardNo':"主卡卡号",'address':"地址",'manager':"联系人",'managerTel':"联系电话",'firstSaleTime':"首次购油时间",'startDay':"开户时间",'annualSaleNumber':"总购油量(吨)"]
		exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
	}
	def marketSaleSumService
	
	def test = {
		def marketList = Market.list()
		Date now = new Date()
		marketList.each{
			(0..now.month).each(){m->
				Date month = new Date(now.year,m,1)
					marketSaleSumService.sumMonthSales(it.id, month)
			}
		}
	}
	
	//update MemberCard where is master to market_card_no
	def updateMarketMemberCard = {
		def memberCardList = MemberCard.createCriteria().list{
			eq("isMaster", true)
		}
		memberCardList.each{
			def m = Market.get(it.marketId)
			m.marketCardNo = it.cardNo
			m.marketType = MarketSpecies.get(2)
		}
    }
	
	def marketReport(Date startDay,Date endDay,Double saleNumberA,Double saleNumberB,areaIds){
		def result,mResult
		if 	(startDay>endDay){
			flash.message = "开始时间不能大于结束时间"
			redirect(action: "report")
		}
		String sb = '''select mgs.market,sum(mgs.saleNumber) from MarketGasSale mgs
						where mgs.saleGasTime <= :endDay and mgs.saleGasTime >= :startDay and mgs.market.marketType.id=2 and mgs.market.area.id in (:areaIds) group by mgs.market'''
		result = MarketGasSale.executeQuery(sb,['startDay':startDay, 'endDay':endDay,'areaIds':areaIds])
		if (!result){
			flash.message = "${message(code: 'default.nofound.message', args: [message(code: 'gasList.label', default: 'GasList')])}"
			redirect(action: "report")
		}
		def mResultList = new ArrayList()
		result.each {
			if((saleNumberA?(it[1]>=saleNumberA):true) && (saleNumberB?(it[1]<=saleNumberB):true)) {
				mResultList.add(it)
			}
		}
		return mResultList
	}
	
	def marketStationReport(Date startDay,Date endDay,Double saleNumberA,Double saleNumberB,station){
		def result,mResult
		if 	(startDay>endDay){
			flash.message = "开始时间不能大于结束时间"
			redirect(action: "report")
		}
		String sb = '''select mgs.market,sum(mgs.saleNumber) from MarketGasSale mgs
						where mgs.saleGasTime <= :endDay and mgs.saleGasTime >= :startDay and mgs.market.marketType.id=2 and mgs.market.station =:station group by mgs.market'''
		result = MarketGasSale.executeQuery(sb,['startDay':startDay, 'endDay':endDay,'station':station])
		if (!result){
			flash.message = "${message(code: 'default.nofound.message', args: [message(code: 'gasList.label', default: 'GasList')])}"
			redirect(action: "report")
		}
		def mResultList = new ArrayList()
		result.each {
			if((saleNumberA?(it[1]>=saleNumberA):true) && (saleNumberB?(it[1]<=saleNumberB):true)) {
				mResultList.add(it)
			}
		}
		return mResultList
	}
	
	def analysis = {
		def result
		double numberA
		Date now = new Date()
		int year = now.year
		int month = now.month
		Date startDay,endDay
		OwnStation station = User.findByUsername(springSecurityService.principal.username).station
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM")
		startDay = sdf.parse((year+1900).toString() + "." + (month+1).toString())
		use(TimeCategory) {
			startDay = startDay - 1.month
			endDay = startDay - 1.month
		}
		def areaIds = []
		def typeIds = []
		if (params.startDay&& params.endDay){
			def cardType = params.cardType
			startDay = params.startDay
			endDay = params.endDay
			numberA = Double.parseDouble((-30).toString())
			if(cardType instanceof String) {
				typeIds.add(cardType)
			} else {
				cardType.each {id->
					typeIds.add(id)
				}
			}
			if(params.stationArea){
				def stationArea = params.stationArea
				if(stationArea instanceof String) {
					areaIds.add(Long.parseLong(stationArea))
				} else {
					stationArea.each {id->
						areaIds.add(Long.parseLong(id))
					}
				}
				result = marketAnalysis(startDay,endDay,areaIds,numberA/100)
			}
			else{
				result = marketStationAnalysis(startDay,endDay,station,numberA/100)
			}
		}
		if(result) {
			result = result.findAll {
				def m = it[0]
				typeIds.contains(m.cardType)
			}
		}
		println "return:${result}"
		[startDay:startDay,endDay:endDay,result:result,stationArea:areaIds,numberA:numberA,cardType:typeIds]
	}

	def marketAnalysis(Date startDay,Date endDay,areaIds,double numberA){
		def result,mResult
		String sb = '''select mgs.market,mgs.saleNumber,mgs.dieselOilNumber,mgs.saleNumber,mgs.saleNumber,mgs.saleNumber,mgs.saleNumber from MarketGasSale mgs
						where mgs.saleGasTime = :startDay and mgs.market.area.id in (:areaIds) group by mgs.market'''
		result = MarketGasSale.executeQuery(sb,['startDay':startDay,'areaIds':areaIds])
		String msb = '''select mgs.market,sum(mgs.saleNumber) from MarketGasSale mgs
			where mgs.saleGasTime = :endDay and mgs.market.area.id in (:areaIds) group by mgs.market'''
		mResult = MarketGasSale.executeQuery(msb,['endDay':endDay,'areaIds':areaIds])
		if (!result&&!mResult){
			flash.message = "${message(code: 'default.nofound.message', )}"
			redirect(action: "analysis")
		}
		def mResultList = new ArrayList()
		mResult.each {
			result.each{r->
				if(r[0] == it[0]) {
					r[2] = it[1]
					r[3] = r[1] - it[1]
					if(r[2] != 0)	r[4] = r[3]/r[2]
					if(r[4]<=numberA)	{
						def marketTrack = getMarketTracking(it[0].id)
						if(marketTrack){
							r[5] = marketTrack[0][2].format("yyyy年MM月dd日")
							r[6] = marketTrack[0][0]
						}
						else {
							r[5] = ""
							r[6] = ""
						}
						mResultList.add(r)
					}
				}
			}
		}
		mResultList.sort{
			-it[4]
		}
		return mResultList
	}
	
	def getMarketTracking(Long id){
		Market market = Market.get(id)
		def marketTracking = MarketTracking.createCriteria().list {
			projections {
				property("id")
				property("market")
				property("handleTime")
			}
			eq("market", market)
			order("handleTime", "desc")
		}
		return marketTracking
	}

	def marketStationAnalysis(Date startDay,Date endDay,OwnStation station,double numberA){
		def result,mResult,mtResult
		String sb = '''select mgs.market,sum(mgs.saleNumber),sum(mgs.saleNumber),sum(mgs.saleNumber),sum(mgs.saleNumber),sum(mgs.saleNumber),sum(mgs.saleNumber) from MarketGasSale mgs
						where mgs.saleGasTime = :startDay and mgs.market.station = :station group by mgs.market'''
		result = MarketGasSale.executeQuery(sb,['startDay':startDay,'station':station])
		String msb = '''select mgs.market,sum(mgs.saleNumber) from MarketGasSale mgs
			where mgs.saleGasTime = :endDay and mgs.market.station = :station group by mgs.market'''
		mResult = MarketGasSale.executeQuery(msb,['endDay':endDay,'station':station])
		
		if (!result&&!mResult){
			flash.message = "${message(code: 'default.nofound.message')}"
			redirect(action: "analysis")
		}
		def mResultList = new ArrayList()
		mResult.each {
			result.each{r->
				if(r[0] == it[0]) {
					r[2] = it[1]
					r[3] = r[1] - it[1]
					if(r[2] != 0)	r[4] = r[3]/r[2]
					if(r[4]<=numberA )	{
						def marketTrack = getMarketTracking(it[0].id)
						if(marketTrack){
							r[5] = marketTrack[0][2].format("yyyy年MM月dd日")
							r[6] = marketTrack[0][0]
						}
						else {
							r[5] = ""
							r[6] = ""
						}
						mResultList.add(r)			
					}
				}
			}
		}
		mResultList.sort{
			-it[4]
		}
		return mResultList
	}
		
	def exportAnalysis ={
		def result
		double numberA
		Date now = new Date()
		int year = now.year
		int month = now.month
		Date startDay,endDay
		OwnStation station = User.findByUsername(springSecurityService.principal.username).station
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM")
		startDay = sdf.parse((year+1900).toString() + "." + (month+1).toString())
		use(TimeCategory) {
			startDay = startDay - 1.month
			endDay = startDay - 1.month
		}
		def areaIds = []
		def typeIds = []
		if (params.startDay&& params.endDay){
			def cardType = params.cardType
			startDay = params.startDay
			endDay = params.endDay
			numberA = Double.parseDouble((30).toString())
			if(params.stationArea){
				def stationArea = params.stationArea
				if(stationArea instanceof String) {
					areaIds.add(Long.parseLong(stationArea))
				} else {
					stationArea.each {id->
						areaIds.add(Long.parseLong(id))
					}
				}
				if(cardType instanceof String) {
					typeIds.add(cardType)
				} else {
					cardType.each {id->
						typeIds.add(id)
					}
				}
				result = marketAnalysis(startDay,endDay,areaIds,numberA/100)
			}
			else{
				result = marketStationAnalysis(startDay,endDay,station,numberA/100)
			}
		}
		
		response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
		response.setHeader("Content-disposition", "attachment; filename=marketAnalysis.xls")
		
		def mapList = new ArrayList<HashMap>()
		result.each {
			def map = new HashMap()
			map.put("area", it[0].area.areaName)
			map.put("cardType", it[0].cardType)
			map.put("managerTel", it[0].managerTel)
			map.put("name", it[0].name)
			map.put("startDay", it[1])
			map.put("endDay", it[2])
			map.put("diff", it[3])
			map.put("rate", it[4]*100)
			map.put("lastTrack", it[5])
			mapList.add(map)
		}
		println mapList
		List fields = ['area','name','cardType','endDay','startDay','diff','rate','managerTel','lastTrack']
		Map labels = ['area':"片区",'cardType':"持卡类型",'name':"客户名称",'startDay':"${startDay.format("MM月")}购油量",'endDay':"${endDay.format("MM月")}购油量",'diff':"增长量",'lastTrack':"最后回访时间",'managerTel':"联系电话",'rate':"增长率(%)"]
		exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
	}
		
    def list = {
        params.max = Math.min(params.max ? params.int('max') : 10, 100)
		def numList = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
		Market.list().each{
			if (it.annualSaleNumber==0||!it.annualSaleNumber) numList[0] ++
			if (it.annualSaleNumber>0 && it.annualSaleNumber<=5) numList[1] ++
			if (it.annualSaleNumber>5 && it.annualSaleNumber<=10) numList[2] ++
			if (it.annualSaleNumber>10 && it.annualSaleNumber<=15) numList[3]++
			if (it.annualSaleNumber>15 && it.annualSaleNumber<=20) numList[4] ++
			if (it.annualSaleNumber>20 && it.annualSaleNumber<=25) numList[5] ++
			if (it.annualSaleNumber>25 && it.annualSaleNumber<=30) numList[6] ++
			if (it.annualSaleNumber>30 && it.annualSaleNumber<=35) numList[7] ++
			if (it.annualSaleNumber>35 && it.annualSaleNumber<=40) numList[8] ++
			if (it.annualSaleNumber>40 && it.annualSaleNumber<=45) numList[9] ++
			if (it.annualSaleNumber>45 && it.annualSaleNumber<=50) numList[10] ++
			if (it.annualSaleNumber>50 && it.annualSaleNumber<=55) numList[11] ++
			if (it.annualSaleNumber>55 && it.annualSaleNumber<=60) numList[12] ++
			if (it.annualSaleNumber>60 && it.annualSaleNumber<=65) numList[13] ++
			if (it.annualSaleNumber>65 && it.annualSaleNumber<=70) numList[14] ++
			if (it.annualSaleNumber>70 && it.annualSaleNumber<=75) numList[15] ++
			if (it.annualSaleNumber>75 && it.annualSaleNumber<=80) numList[16] ++
			if (it.annualSaleNumber>80 && it.annualSaleNumber<=85) numList[17] ++
			if (it.annualSaleNumber>85 && it.annualSaleNumber<=90) numList[18] ++
			if (it.annualSaleNumber>90 && it.annualSaleNumber<=95) numList[19] ++
			if (it.annualSaleNumber>95 && it.annualSaleNumber<=100) numList[20] ++
		}
		 def marketSum = "t:${numList[0]}"
		 numList.each{m->
				 marketSum += "," + m
		 }
		 OwnStation station1 = User.findByUsername(springSecurityService.principal.username).station
  		 def station2 = params['station.id']?OwnStation.get(params['station.id']):null
		 def s = springSecurityService.principal.username=='admin'?'':station1
		 def station =  station2?station2:s
		 def result = Market.createCriteria().list([max:params.max, offset:params.offset]){
			 if(station){
				 eq("station",station)
			 }
		 }
        [marketInstanceList: result, marketInstanceTotal: result.totalCount, marketSum:marketSum]
    }
	
	def editList = {
		params.max = Math.min(params.max ? params.int('max') : 10, 100)
        [marketInstanceList: Market.list(params), marketInstanceTotal: Market.count()]
	}
	
	
	def unLock = {
		def marketInstance = Market.get(params.id)
        flash.message = "${message(code: 'default.updated.message', args: [message(code: 'market.label', default: 'Market'), marketInstance.id])}"
        if (marketInstance) {
			marketInstance.isLocked = false 
			redirect(action: "editList")
		}
		else {
			redirect(action: "editList")
		}
	}
	def locked = {
		def marketInstance = Market.get(params.id)
		flash.message = "${message(code: 'default.updated.message', args: [message(code: 'market.label', default: 'Market'), marketInstance.id])}"
		if (marketInstance) {
			marketInstance.isLocked = true
			redirect(action: "editList")
		}
		else {
			redirect(action: "editList")
		}
	}
	def exportService
	def springSecurityService
	
	def parseGasSale (Market market,Date firstSight,Date endSight) {
		String sql = '''select cc.member.market.name,month(cc.tradeTime),sum(cc.amount),cc.tradeTime 
						from CardConsume cc where cc.tradeTime >= :start and cc.tradeTime <= :end and cc.member.market = :market
						group by month(cc.tradeTime)'''
		def result = CardConsume.executeQuery(sql,["start":firstSight,"end":endSight,"market":market])
		def i = 1
		def mapList = new ArrayList<HashMap>()
		(1.. 12).each{m->
			def map = new HashMap()
			result.each{r->
				if (r[1]==m) {
					map.put(m,r[2]/1000)
				}
			}
			if (!map.get(m)) {
				map.put(m,0)
			}
			mapList.add(map)
		}
		def mk = new HashMap()
		mk.put("market", market.name)
		mapList.add(mk)
		return mapList
	}
	
	
	def loadSales = {
		def marketInstance = Market.get(params.id)
		def memberCard = MemberCard.findAllByMarket(marketInstance)
		memberCard.each{m->
			def cardConsumeRaw = CardConsumeRaw.findAllByCardNo(m.cardNo)
			if(cardConsumeRaw){
				cardConsumeRaw.each{
					CardConsume cardConsume = new CardConsume(member:m,rawInfo:it,
						tradeAt: it.tradeAt, gasType: GasTypeRaw.findByTypeName(it.goodsType).type)
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")
					Date tradeTime = sdf.parse(it.tradeTime)
					cardConsume.amount = Float.parseFloat(it.volume)
					cardConsume.tradeTime = tradeTime
					cardConsume.save(flush:true)
					println cardConsume
				}
			}
		}
		redirect(action: "show", id: marketInstance.id)
	}
	
	def loadAllSale = {
		def marketList = Market.list()
		def memberCard = MemberCard.list()
		memberCard.each{m->
			def cardConsumeRaw = CardConsumeRaw.findAllByCardNo(m.cardNo)
				cardConsumeRaw.each{
					if(cardConsumeRaw&&GasTypeRaw.findByTypeName(it.goodsType)){
						CardConsume cardConsume = new CardConsume(member:m,rawInfo:it,
							tradeAt: it.tradeAt, gasType: GasTypeRaw.findByTypeName(it.goodsType).type)
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")
						Date tradeTime = sdf.parse(it.tradeTime)
						cardConsume.amount = Float.parseFloat(it.volume)
						cardConsume.tradeTime = tradeTime
						cardConsume.save(flush:true)
						println cardConsume
					}
			}
		}
		redirect(action: "list")
	}

	def export = {
		response.contentType = ConfigurationHolder.config.grails.mime.types['excel']
		response.setHeader("Content-disposition", "attachment; filename=market.xls")
		def result = Market.list()
		Date now = new Date()
		def months = now.month
		def year = now.year + 1900
		def mapList = new ArrayList<HashMap>()
		result.each {
			def map = new HashMap()
			if (it.area){
				map.put("area", it.area.areaName)
			}
			else map.put("area", "")
			map.put("marketName", it.marketName)
			map.put("name", it.name)
			map.put("marketType", it.marketType.type)
			map.put("marketCardNo", it.marketCardNo)
			map.put("address", it.address)
			map.put("managerTel", it.managerTel)
			map.put("manager", it.manager)
			map.put("firstSaleTime", it.firstSaleTime)
			map.put("discountNum", it.discountNum)
			map.put("annualSaleNumber", it.annualSaleNumber)
			map.put("startTime", it.cardMarketTime)
			map.put("station", it.station.shortName)
			map.put("provideSite", it.provideSite)
			map.put("gasPredictSale", it.gasPredictSale)
			map.put("fuelPredictSale", it.fuelPredictSale)
			map.put("betweenDistance", it.betweenDistance)
			map.put("requirement", it.requirement)
			map.put("carePoint", it.carePoint)
			map.put("type", it.type.title)
			map.put("cardType", it.cardType)
			map.put("latitude", it.latitude)
			map.put("longitude", it.longitude)
			def firstSight = new Date(now.year,0,1)
			def endSight = new Date(now.year+1,0,1)
			def sale = parseGasSale(it,firstSight,endSight)
			(1.. months+1).each{m->
				map.put(m.toString(), sale.get(m-1).get(m))
			}
			mapList.add(map)
		}
		List fields = ["area","name","marketCardNo","marketType","type","cardType","address","manager","managerTel","firstSaleTime","startTime","provideSite","betweenDistance","requirement","carePoint","discountNum","gasPredictSale","fuelPredictSale","annualSaleNumber","station","longitude","latitude"]
		Map labels = ["area":"所在片区","marketName":"客户编码","name":"客户名称",'marketType':"客户分类","type":"客户类型","cardType":"持卡性质","marketCardNo":"主卡卡号","address":"地址","manager":"联系人","managerTel":"联系电话","provideSite":"供油单位","betweenDistance":"客户到加油站的距离（公里）","requirement":"客户诉求","carePoint":"客户关注点","firstSaleTime":"首次消费时间","startTime":"开户时间","discountNum":"价格优惠额度","station":"隶属加油站","annualSaleNumber":"${year}年度累计购油量(吨)","gasPredictSale":"汽油预估销量(吨/月)","fuelPredictSale":"柴油预估销量(吨/月)","latitude":"经度","longitude":"纬度"]
		(1.. 12).each {m->
			fields.add(m.toString())
			labels.put(m.toString(), m + "月(吨)")
		}
		exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])
	}
	
    def create = {
        def marketInstance = new Market()
		marketInstance.latitude = params.lat
		marketInstance.longitude = params.lng
        marketInstance.properties = params
        return [marketInstance: marketInstance]
    }

    def save = {
        def marketInstance = new Market(params)
		marketInstance.createTime = new Date()
		OwnStation station = User.findByUsername(springSecurityService.principal.username).station
		marketInstance.station = station
		marketInstance.area = station.stationArea
		marketInstance.marketName = 1
		marketInstance.isLocked = false
		marketInstance.marketCardNo = 0
		marketInstance.marketType = MarketSpecies.get(1)
		marketInstance.detailedInfoValiable = false
        if (marketInstance.save(flush: true)) {
            flash.message = "${message(code: 'default.created.message', args: [message(code: 'market.label', default: 'Market'), marketInstance.id])}"
            redirect(action: "show", id: marketInstance.id)
        }
        else {
            render(view: "create", model: [marketInstance: marketInstance])
        }
    }

    def show = {
        def marketInstance = Market.get(params.id)
        if (!marketInstance) {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'market.label', default: 'Market'), params.id])}"
            redirect(action: "list")
        }
        else {
			Date now = new Date()
			def years = now.year+1900
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy")
			Date year,nextYear,month,nextMonth
			year = sdf.parse((now.year+1900).toString())
			def mm = params.month?params.month.month:now.month
			def yy = params.month?params.month.year:now.year
			def firstSight = new Date(year.year,0,1)
			def endSight = new Date(year.year+1,0,1)
			month = new Date(yy,mm,1)
			nextMonth = new Date(yy,mm+1,1)
			params.max = Math.min(params.max ? params.int('max') : 8, 100)
			def categories = GasTypeCategory.list()
			def sb = "select cc.member, sum(cc.amount), cc.gasType.category from CardConsume cc where cc.tradeTime <= :endDay and cc.tradeTime >= :startDay and cc.member.market=:market group by cc.gasType.category,cc.member"
			def cardConsumeSales = CardConsume.executeQuery(sb.toString(),['startDay':month, 'endDay':nextMonth,'market':marketInstance])
			def num = parseGasSale(marketInstance,firstSight,endSight)
			println num
			def numList = "0"
			(1.. 12).each{m->
				if (m<num.size()){
					if (num.get(m-1)){numList += "," + num.get(m-1).get(m)}
					else numList += "," + 0
				}
				else numList += "," + 0
			}
			def scb = "select count(*) from MemberCard mc where mc.market=:market "
			def cardsum = CardConsume.executeQuery(scb.toString(),['market':marketInstance])
			def cardSums = cardsum[0]
            [marketInstance: marketInstance,numList:numList,cardConsumeSales:cardConsumeSales,years:years,year:yy+1900,mm:mm+1,month:params.month,cardSums:cardSums]
        }
    }

    def edit = {
        def marketInstance = Market.get(params.id)
        if (!marketInstance) {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'market.label', default: 'Market'), params.id])}"
            redirect(action: "list")
        }
        else {
            return [marketInstance: marketInstance]
        }
    }
	
	def updateSale = {
		def marketInstance = Market.get(params.id)
		if (marketInstance) {
			if (params.version) {
				def version = params.version.toLong()
				if (marketInstance.version > version) {
					marketInstance.errors.rejectValue("version", "default.optimistic.locking.failure", [message(code: 'ownStation.label', default: 'OwnStation')] as Object[], "Another user has updated this OwnStation while you were editing")
					render(view: "edit", model: [marketInstance: marketInstance])
					return
				}
			}
			marketInstance.properties = params
			if (!marketInstance.hasErrors()) {
				def time = params.saleGasTime
				def saleNumber = params.saleNumber
				def saleTime = params.saleTime
				if(!saleNumber||!saleTime) {
					flash.message = "${message(code: 'default.error.message')}"
					redirect(action: "show", id: marketInstance.id)
				}
				else{
					Date now = new Date()
					float sn = Float.parseFloat(saleNumber)
					int no = Integer.parseInt(saleTime)
					if (time > now||sn > 100){
						flash.message = "${message(code: 'default.errorInput.message')}"
						redirect(action: "show", id: marketInstance.id)
					}
					else{
						MarketGasSale marketGasSale = new MarketGasSale('saleNumber':saleNumber)
						marketGasSale.saleGasTime = time
						marketGasSale.saleTime = no
						marketGasSale.market = marketInstance
						try{
							marketInstance.addToMarketGasSales(marketGasSale)
							marketGasSale.save(flush:true)
						}catch(e) {
							e.printStackTrace()
						}
						flash.message = "${message(code: 'default.updated.message', args: [message(code: 'market.label', default: 'market'), marketInstance.id])}"
						redirect(action: "show", id: marketInstance.id)
					}
				}
			}
			else {
				flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'market.label', default: 'market'), params.id])}"
				redirect(action: "list")
			}
		}
	}
	
    def update = {
        def marketInstance = Market.get(params.id)
        flash.message = "${message(code: 'default.updated.message', args: [message(code: 'market.label', default: 'Market'), marketInstance.id])}"
        if (marketInstance) {
            if (params.version) {
                def version = params.version.toLong()
                if (marketInstance.version > version) {
                    marketInstance.errors.rejectValue("version", "default.optimistic.locking.failure", [message(code: 'market.label', default: 'Market')] as Object[], "Another user has updated this Market while you were editing")
                    render(view: "edit", model: [marketInstance: marketInstance])
                    return
                }
            }
            marketInstance.properties = params
            if (!marketInstance.hasErrors() && marketInstance.save(flush: true)) {
					flash.message = "${message(code: 'default.updated.message', args: [message(code: 'market.label', default: 'Market'), marketInstance.id])}"
	                redirect(action: "show", id: marketInstance.id)
            }
	        else {
	            flash.message = "${message(code: 'default.error.message', args: [message(code: 'market.label', default: 'Market'), params.id])}"
	            redirect(action: "show", id: marketInstance.id)
	        }
        }
    }

    def delete = {
        def marketInstance = Market.get(params.id)
        if (marketInstance) {
            try {
                marketInstance.delete(flush: true)
                flash.message = "${message(code: 'default.deleted.message', args: [message(code: 'market.label', default: 'Market'), params.id])}"
                redirect(action: "list")
            }
            catch (org.springframework.dao.DataIntegrityViolationException e) {
                flash.message = "${message(code: 'default.not.deleted.message', args: [message(code: 'market.label', default: 'Market'), params.id])}"
                redirect(action: "show", id: params.id)
            }
        }
        else {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'market.label', default: 'Market'), params.id])}"
            redirect(action: "list")
        }
    }
	
	private String getExcelCellValue(Cell cell) {
		if(cell) {
			if(cell.cellType == Cell.CELL_TYPE_NUMERIC) {
				int value = cell.numericCellValue
				return value
			}
			if(cell.cellType == Cell.CELL_TYPE_STRING) {
				return cell.stringCellValue
			}
		}
		return null
	}
	
	def loadRawToCardConsum = {
		def cardList = MemberCard.createCriteria().list(){
			projections {
				property ("cardNo")
			}
		}
		def rawList = CardConsumeRaw.createCriteria().list(){
			ge("tradeTime", '2012-11-28 16:00:00')
			'in'("cardNo", cardList)
		}
		rawList.each{row->
			println row
			def memberCard = MemberCard.findByCardNo(row.cardNo, [cache:true])
			GasTypeRaw gasType = GasTypeRaw.findByTypeName(row.goodsType, [cache:true])
			if(memberCard&&gasType) {
				println row
				CardConsume cardConsume = new CardConsume(member:memberCard,rawInfo:row,
					tradeAt: row.tradeAt, gasType: gasType.type)
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss")
				Date tradeTime = sdf.parse(row.tradeTime)
				cardConsume.amount = Float.parseFloat(row.volume)
				cardConsume.tradeTime = tradeTime
				println "${memberCard},${row.tradeAt},${gasType.type},${row.volume}"
				cardConsume.save(flush:true)
			}
		}
		println rawList.size()
		
	}
}
 